package com.xiashitech.sqlparser.sqlvisitor.finder;

import com.google.auto.service.AutoService;
import com.xiashitech.interfaces.sqlparser.SqlParser;
import com.xiashitech.interfaces.sqlparser.dto.ColumnRelationDTO;
import com.xiashitech.interfaces.sqlparser.dto.ResultDTO;
import com.xiashitech.interfaces.sqlparser.dto.TableFinderDTO;
import com.xiashitech.sqlparser.sqlvisitor.Constants.ParserConstant;
import com.xiashitech.sqlparser.sqlvisitor.context.ColumnFinderContext;
import com.xiashitech.sqlparser.utils.SqlMatchUtil;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.alter.Alter;
import net.sf.jsqlparser.statement.create.index.CreateIndex;
import net.sf.jsqlparser.statement.create.schema.CreateSchema;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.create.view.AlterView;
import net.sf.jsqlparser.statement.create.view.CreateView;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.drop.Drop;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.merge.Merge;
import net.sf.jsqlparser.statement.replace.Replace;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.truncate.Truncate;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.upsert.Upsert;
import net.sf.jsqlparser.util.TablesNamesFinder;

import java.io.StringReader;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;

/**
 * 继承TablesNamesFinder的sql语句的遍历访问能力，解析sql，提取出来表名和表所对应的列名，
 * 然后将提取出来的结果装在到
 */
@AutoService(SqlParser.class)
public class TableAndColumnFinder extends TablesNamesFinder implements SqlParser {

    private ThreadLocal<ResultDTO> resultModel = new ThreadLocal();

    public TableAndColumnFinder() {
        refreshResultModel();
    }

    private void refreshResultModel() {
        resultModel.set(ResultDTO.builder().build());
        resultModel.get().setTableFinderDTOS(new ArrayList<TableFinderDTO>());
        resultModel.get().setColumnRelationDTOS(new ArrayList<ColumnRelationDTO>());
        ColumnFinderContext.refreshCurrentThreadLocal();
    }

    /**
     * 查找用户访问了哪些表，以及访问了这些表的哪些字段
     * @param sql 传入待解析的sql
     * @return ResultModel 返回的结果，
     * @throws Exception
     */
    @Override
    public ResultDTO  find(String sql) throws Exception {
        System.out.println("TableAndColumnFinder find sql: " + sql);
        refreshResultModel();
        if(SqlMatchUtil.canNotParsedSql(sql)) {
            return resultModel.get();
        }
        CCJSqlParserManager pm = new CCJSqlParserManager();
        Statement statement = pm.parse(new StringReader(sql));
        if(statement instanceof Select) {
            resultModel.get().setSqlType(ParserConstant.SELECT);
        } else if (statement instanceof Insert) {
            resultModel.get().setSqlType(ParserConstant.INSERT);
        } else if (statement instanceof Update) {
            resultModel.get().setSqlType(ParserConstant.UPDATE);
        } else if (statement instanceof Delete) {
            resultModel.get().setSqlType(ParserConstant.DELETE);
        } else if (statement instanceof Drop) {
            throw new UnsupportedOperationException("Operation is not supported for : Drop");
        } else if (statement instanceof Truncate) {
            throw new UnsupportedOperationException("Operation is not supported for : Truncate");
        } else if (statement instanceof Alter) {
            throw new UnsupportedOperationException("Operation is not supported for : Alter");
        } else if (statement instanceof Upsert) {
            throw new UnsupportedOperationException("Operation is not supported for : Upsert");
        } else if (statement instanceof Merge) {
            throw new UnsupportedOperationException("Operation is not supported for : Merge");
        } else if (statement instanceof AlterView) {
            throw new UnsupportedOperationException("Operation is not supported for : AlterView");
        } else if (statement instanceof CreateView) {
            throw new UnsupportedOperationException("Operation is not supported for : CreateView");
        } else if (statement instanceof CreateTable) {
            throw new UnsupportedOperationException("Operation is not supported for : CreateTable");
        } else if (statement instanceof CreateSchema) {
            throw new UnsupportedOperationException("Operation is not supported for : CreateSchema");
        } else if (statement instanceof CreateIndex) {
            throw new UnsupportedOperationException("Operation is not supported for : CreateIndex");
        } else if (statement instanceof Replace) {
            throw new UnsupportedOperationException("Operation is not supported for : Replace");
        }
        init(false);
        statement.accept(this);
        return resultModel.get();
    }

    /**
     * 解析Insert语句，将SQL里访问了具体真实的哪些表和字段的关系提取出来并存储到TableFinderModel，结果以ResultModel形式返回。
     * @param insert
     */
    @Override
    public void visit(Insert insert) {
        super.visit(insert);
        setTableAndColumn(insert.getTable(),Optional.ofNullable(insert.getColumns()),true);
    }

    /**
     * 解析Update语句，将SQL里访问了具体真实的哪些表和字段的关系提取出来并存储到TableFinderModel，结果以ResultModel形式返回。
     * @param update
     */
    @Override
    public void visit(Update update) {
        super.visit(update);
        setTableAndColumn(update.getTable(),Optional.ofNullable(update.getUpdateSets().stream().map(updateSet -> updateSet.getColumns()).flatMap(columns -> columns.stream()).collect(Collectors.toList())),true);
    }

    /**
     * 解析Delete语句，将SQL里访问了具体真实的哪些表和字段的关系提取出来并存储到TableFinderModel，结果以ResultModel形式返回。
     * @param delete
     */
    @Override
    public void visit(Delete delete) {
        super.visit(delete);
        setTableAndColumn(delete.getTable(),Optional.empty(),false);
    }

    /**
     * 覆盖TablesNamesFinder相关方法，将SQL里访问了具体真实的哪些表和字段的关系提取出来并存储到TableFinderModel，结果以ResultModel形式返回。
     */
    @Override
    public void visit(PlainSelect plainSelect) {
        ColumnFinderContext.putCurrentVar(plainSelect);
        if (plainSelect.getSelectItems() != null) {
            for (SelectItem item : plainSelect.getSelectItems()) {
                item.accept(this);
            }
        }

        if (plainSelect.getFromItem() != null) {
            plainSelect.getFromItem().accept(this);
        }

        if (plainSelect.getJoins() != null) {
            for (Join join : plainSelect.getJoins()) {
                join.getRightItem().accept(this);
            }
        }
        if (plainSelect.getWhere() != null) {
            plainSelect.getWhere().accept(this);
        }

        if (plainSelect.getHaving() != null) {
            plainSelect.getHaving().accept(this);
        }

        if (plainSelect.getOracleHierarchical() != null) {
            plainSelect.getOracleHierarchical().accept(this);
        }
        ColumnFinderContext.removeCurrentVar();
    }

    /**
     * 针对表访问处理
     */
    @Override
    public void visit(Table tableName) {
        super.visit(tableName);
        Long tableCount = resultModel.get().getTableFinderDTOS().stream().filter(tableFinderDTO -> tableName.getName().equals(tableFinderDTO.getTable())).count();
        if(tableCount == 0L) {
            TableFinderDTO tableFinderDTO = TableFinderDTO.builder().table(tableName.getName()).build();
            tableFinderDTO.setColumns(new ArrayList<String>());
            resultModel.get().getTableFinderDTOS().add(tableFinderDTO);
        }
    }

    /**
     * 针对列访问处理
     * 覆盖TablesNamesFinder相关方法，将SQL里访问了具体真实的哪些表和字段的关系提取出来并存储到TableFinderModel，结果以ResultModel形式返回。
     */
    @Override
    public void visit(Column tableColumn) {
        super.visit(tableColumn);
        Optional<PlainSelect> optionalPlainSelect = Optional.ofNullable(ColumnFinderContext.getCurrentVar());
        if(optionalPlainSelect.isPresent()) {
            if(optionalPlainSelect.get().getFromItem() instanceof Table) {
                setColumn((Table)optionalPlainSelect.get().getFromItem(), tableColumn);
            }
            Optional<List<Join>> optionalJoinList = Optional.ofNullable(optionalPlainSelect.get().getJoins());
            if(optionalJoinList.isPresent()) {
                optionalJoinList.get().stream().filter(join -> join.getRightItem() instanceof  Table).forEach(join -> setColumn((Table)join.getRightItem(), tableColumn));
            }
        }
    }

    /**
     * 针对所有列访问处理
     * 覆盖TablesNamesFinder相关方法，将SQL里访问了具体真实的哪些表和字段的关系提取出来并存储到TableFinderModel，结果以ResultModel形式返回。
     */
    @Override
    public void visit(AllColumns allColumns) {
        super.visit(allColumns);
        PlainSelect plainSelect = ColumnFinderContext.getCurrentVar();
        if(plainSelect.getFromItem() instanceof Table) {
            setAllColumns((Table)plainSelect.getFromItem(),true);
        }
        Optional<List<Join>> optionalJoinList = Optional.ofNullable(plainSelect.getJoins());
        if(optionalJoinList.isPresent()) {
            optionalJoinList.get().stream().filter(join -> join.getRightItem() instanceof Table).forEach(join -> setAllColumns((Table) join.getRightItem(),true));
        }
    }

    /**
     * 针对某一个表的全列查询访问处理
     * 覆盖TablesNamesFinder相关方法，将SQL里访问了具体真实的哪些表和字段的关系提取出来并存储到TableFinderModel，结果以ResultModel形式返回。
     */
    @Override
    public void visit(AllTableColumns allTableColumns) {
        super.visit(allTableColumns);
        PlainSelect plainSelect = ColumnFinderContext.getCurrentVar();
        if(plainSelect.getFromItem() instanceof Table) {
            setAllTableColumns((Table)plainSelect.getFromItem(),allTableColumns);
        }
        Optional<List<Join>> optionalJoinList = Optional.ofNullable(plainSelect.getJoins());
        if(optionalJoinList.isPresent()) {
            optionalJoinList.get().stream().filter(join -> join.getRightItem() instanceof Table).forEach(join -> setAllTableColumns((Table) join.getRightItem(), allTableColumns));
        }
    }

    /**
     * 针对每一个PlainSelect，进行真实表和字段的关联关系建立，设置ResultModel里tableFinderModelList的值
     */
    private void setColumn(Table plainSelectTable, Column column) {
        String columnName = column.getColumnName();
        String name = plainSelectTable.getName();
        Optional<Table> table = Optional.ofNullable(column.getTable());
        if(table.isPresent()) {
            Optional<Alias> tableAlias = Optional.ofNullable(plainSelectTable.getAlias());
            if(tableAlias.isPresent() && tableAlias.get().getName().equals(table.get().getName())) {
                List<TableFinderDTO> tableFinderModelList = resultModel.get().getTableFinderDTOS();
                Optional<TableFinderDTO> tableFinderModelOptional = tableFinderModelList.stream().filter(tableFinderModel -> tableFinderModel.getTable().equals(name)).findFirst();
                if (tableFinderModelOptional.isPresent()) {
                    if(!tableFinderModelOptional.get().getColumns().contains(columnName)) {
                        tableFinderModelOptional.get().getColumns().add(columnName);
                    }
                } else {
                    TableFinderDTO tableFinderModel = TableFinderDTO.builder().table(name).build();
                    List<String> columnList = new ArrayList<String>();
                    columnList.add(columnName);
                    tableFinderModel.setColumns(columnList);
                    tableFinderModelList.add(tableFinderModel);
                }
            }
        } else {
            List<TableFinderDTO> tableFinderModelList = resultModel.get().getTableFinderDTOS();
            Optional<TableFinderDTO> tableFinderModelOptional = tableFinderModelList.stream().filter(tableFinderModel -> tableFinderModel.getTable().equals(name)).findFirst();
            if (tableFinderModelOptional.isPresent()) {
                if(!tableFinderModelOptional.get().getColumns().contains(columnName)) {
                    tableFinderModelOptional.get().getColumns().add(columnName);
                }
            } else {
                TableFinderDTO tableFinderModel = TableFinderDTO.builder().table(name).build();
                List<String> columnList = new ArrayList<String>();
                columnList.add(columnName);
                tableFinderModel.setColumns(columnList);
                tableFinderModelList.add(tableFinderModel);
            }
        }
    }

    /**
     * 针对每一个PlainSelect，进行真实表和字段的关联关系建立，设置ResultModel里tableFinderModelList的值，当为某一个表的全列查询时调用，例如：select t.* from table t
     */
    private void setAllTableColumns(Table plainSelectTable, AllTableColumns allTableColumns) {
        Optional<Table> columnTable = Optional.ofNullable(allTableColumns.getTable());
        Optional<Alias> tableAlias = Optional.ofNullable(plainSelectTable.getAlias());
        if(tableAlias.isPresent() && columnTable.isPresent() && tableAlias.get().getName().equals(columnTable.get().getName())) {
            List<TableFinderDTO> tableFinderModelList = resultModel.get().getTableFinderDTOS();
            Optional<TableFinderDTO> tableFinderModelOptional = tableFinderModelList.stream().filter(tableFinderModel -> tableFinderModel.getTable().equals(plainSelectTable.getName())).findFirst();
            if (tableFinderModelOptional.isPresent()) {
                tableFinderModelOptional.get().setAllColumn(true);
            } else {
                TableFinderDTO tableFinderModel = TableFinderDTO.builder().table(plainSelectTable.getName()).allColumn(true).build();
                tableFinderModel.setColumns(new ArrayList());
                tableFinderModelList.add(tableFinderModel);
            }
        }
    }

    /**
     * 针对每一个PlainSelect，进行真实表和字段的关联关系建立，设置ResultModel里tableFinderModelList的值，为所有表的全列查询时调用，例如：select * from table t
     */
    private void setAllColumns(Table plainSelectTable, Boolean isAllColumn) {
        List<TableFinderDTO> tableFinderModelList = resultModel.get().getTableFinderDTOS();
        Optional<TableFinderDTO> tableFinderModelOptional = tableFinderModelList.stream().filter(tableFinderModel -> tableFinderModel.getTable().equals(plainSelectTable.getName())).findFirst();
        if (tableFinderModelOptional.isPresent()) {
            tableFinderModelOptional.get().setAllColumn(isAllColumn);
        } else {
            TableFinderDTO tableFinderModel = TableFinderDTO.builder().table(plainSelectTable.getName()).allColumn(isAllColumn).build();
            tableFinderModel.setColumns(new ArrayList<String>());
            tableFinderModelList.add(tableFinderModel);
        }
    }

    /**
     * 将表和其对应的列存储到ResultModel的tableFinderModelList里的TableFinderModel对象中
     */
    private void setTableAndColumn(Table table,Optional<List<Column>> optionalColumnList,Boolean isHavingColumn) {
        List<TableFinderDTO> tableFinderModelList = resultModel.get().getTableFinderDTOS();
        Optional<TableFinderDTO> tableFinderModelOptional = tableFinderModelList.stream().filter(tableFinderModel -> tableFinderModel.getTable().equals(table.getName())).findFirst();
        if(optionalColumnList.isPresent()) {
            if (tableFinderModelOptional.isPresent()) {
                optionalColumnList.get().stream().filter(column -> !tableFinderModelOptional.get().getColumns().contains(column.getColumnName())).forEach(column -> tableFinderModelOptional.get().getColumns().add(column.getColumnName()));
            } else {
                TableFinderDTO tableFinderModel = TableFinderDTO.builder().table(table.getName()).build();
                List<String> columnList = new ArrayList<String>();
                columnList.addAll(optionalColumnList.get().stream().map(column -> column.getColumnName()).collect(Collectors.toList()));
                tableFinderModel.setColumns(columnList);
                tableFinderModelList.add(tableFinderModel);
            }
        } else {
            setAllColumns(table,isHavingColumn);
        }
    }
}
